
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/02/2013 16:26:24
-- Generated from EDMX file: C:\Users\it001\Desktop\QSHC\Q4QSHC\src\QSchedule\QSHC.QSchedule.Web\Models\QScheduleModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [QSchedule];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ServicePhysicianSchedule]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PhysicianSchedules] DROP CONSTRAINT [FK_ServicePhysicianSchedule];
GO
IF OBJECT_ID(N'[dbo].[FK_AppointedPatientAppointment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Appointments] DROP CONSTRAINT [FK_AppointedPatientAppointment];
GO
IF OBJECT_ID(N'[dbo].[FK_AppointmentAppointmentDetail]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AppointmentDetails] DROP CONSTRAINT [FK_AppointmentAppointmentDetail];
GO
IF OBJECT_ID(N'[dbo].[FK_PreOrderAppointmentDetail]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AppointmentDetails] DROP CONSTRAINT [FK_PreOrderAppointmentDetail];
GO
IF OBJECT_ID(N'[dbo].[FK_ServiceAppointment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Appointments] DROP CONSTRAINT [FK_ServiceAppointment];
GO
IF OBJECT_ID(N'[dbo].[FK_HolidayLogHoliday]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LogHolidays] DROP CONSTRAINT [FK_HolidayLogHoliday];
GO
IF OBJECT_ID(N'[dbo].[FK_PhysicianScheduleLogPhysicianSchedule]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LogPhysicianSchedules] DROP CONSTRAINT [FK_PhysicianScheduleLogPhysicianSchedule];
GO
IF OBJECT_ID(N'[dbo].[FK_AppointmentLogAppointment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LogAppointments] DROP CONSTRAINT [FK_AppointmentLogAppointment];
GO
IF OBJECT_ID(N'[dbo].[FK_ServicePhysicianAbsent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PhysicianAbsents] DROP CONSTRAINT [FK_ServicePhysicianAbsent];
GO
IF OBJECT_ID(N'[dbo].[FK_PhysicianAbsentLogPhysicianAbsent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[LogPhysicianAbsents] DROP CONSTRAINT [FK_PhysicianAbsentLogPhysicianAbsent];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[PhysicianSchedules]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PhysicianSchedules];
GO
IF OBJECT_ID(N'[dbo].[Services]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Services];
GO
IF OBJECT_ID(N'[dbo].[Holidays]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Holidays];
GO
IF OBJECT_ID(N'[dbo].[Appointments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Appointments];
GO
IF OBJECT_ID(N'[dbo].[AppointedPatients]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AppointedPatients];
GO
IF OBJECT_ID(N'[dbo].[AppointmentDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AppointmentDetails];
GO
IF OBJECT_ID(N'[dbo].[PreOrders]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PreOrders];
GO
IF OBJECT_ID(N'[dbo].[LogAppointments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LogAppointments];
GO
IF OBJECT_ID(N'[dbo].[LogPhysicianSchedules]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LogPhysicianSchedules];
GO
IF OBJECT_ID(N'[dbo].[LogHolidays]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LogHolidays];
GO
IF OBJECT_ID(N'[dbo].[PhysicianAbsents]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PhysicianAbsents];
GO
IF OBJECT_ID(N'[dbo].[LogPhysicianAbsents]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LogPhysicianAbsents];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'PhysicianSchedules'
CREATE TABLE [dbo].[PhysicianSchedules] (
    [PhysicianScheduleID] int IDENTITY(1,1) NOT NULL,
    [Physician] nvarchar(5)  NOT NULL,
    [ServiceID] int  NOT NULL,
    [DayOfWeek] int  NOT NULL,
    [Quota] int  NOT NULL,
    [BeginDate] datetime  NOT NULL,
    [EndDate] datetime  NULL,
    [IsDeleted] bit  NOT NULL
);
GO

-- Creating table 'Services'
CREATE TABLE [dbo].[Services] (
    [ServiceID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Phone] nvarchar(25)  NULL,
    [DefaultAppointmentTime] nvarchar(10)  NOT NULL
);
GO

-- Creating table 'Holidays'
CREATE TABLE [dbo].[Holidays] (
    [HolidayID] int IDENTITY(1,1) NOT NULL,
    [HolidayDate] datetime  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Remark] nvarchar(255)  NULL,
    [IsDeleted] bit  NOT NULL
);
GO

-- Creating table 'Appointments'
CREATE TABLE [dbo].[Appointments] (
    [AppointmentID] int IDENTITY(1,1) NOT NULL,
    [HN] nvarchar(8)  NOT NULL,
    [AppointmentDate] datetime  NOT NULL,
    [AppointmentTime] nvarchar(10)  NOT NULL,
    [ServiceID] int  NOT NULL,
    [Physician] nvarchar(5)  NOT NULL,
    [VisitDate] datetime  NULL,
    [Remark] nvarchar(max)  NULL,
    [IsDeleted] bit  NOT NULL
);
GO

-- Creating table 'AppointedPatients'
CREATE TABLE [dbo].[AppointedPatients] (
    [HN] nvarchar(8)  NOT NULL,
    [Title] nvarchar(50)  NOT NULL,
    [FirstName] nvarchar(50)  NOT NULL,
    [LastName] nvarchar(50)  NOT NULL,
    [Sex] int  NOT NULL,
    [BirthDate] datetime  NULL,
    [PIN] nvarchar(13)  NULL,
    [Payor] nvarchar(50)  NULL,
    [Address] nvarchar(255)  NULL,
    [Tambon] nvarchar(100)  NULL,
    [Amphur] nvarchar(100)  NULL,
    [Province] nvarchar(100)  NULL,
    [Phone] nvarchar(50)  NULL
);
GO

-- Creating table 'AppointmentDetails'
CREATE TABLE [dbo].[AppointmentDetails] (
    [AppointmentDetailID] int IDENTITY(1,1) NOT NULL,
    [AppointmentID] int  NOT NULL,
    [PreOrderID] int  NOT NULL
);
GO

-- Creating table 'PreOrders'
CREATE TABLE [dbo].[PreOrders] (
    [PreOrderID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(100)  NOT NULL,
    [Description] nvarchar(255)  NULL,
    [Type] int  NOT NULL,
    [IsQuotaCount] bit  NOT NULL
);
GO

-- Creating table 'LogAppointments'
CREATE TABLE [dbo].[LogAppointments] (
    [LogAppointmentID] int IDENTITY(1,1) NOT NULL,
    [EmployeeCode] nvarchar(5)  NOT NULL,
    [LogDateTime] datetime  NOT NULL,
    [Action] int  NOT NULL,
    [PostponeRemark] nvarchar(255)  NULL,
    [AppointmentID] int  NOT NULL
);
GO

-- Creating table 'LogPhysicianSchedules'
CREATE TABLE [dbo].[LogPhysicianSchedules] (
    [LogPhysicianScheduleID] int IDENTITY(1,1) NOT NULL,
    [EmployeeCode] nvarchar(5)  NOT NULL,
    [LogDateTime] datetime  NOT NULL,
    [Action] int  NOT NULL,
    [PhysicianScheduleID] int  NOT NULL
);
GO

-- Creating table 'LogHolidays'
CREATE TABLE [dbo].[LogHolidays] (
    [LogHolidayID] int IDENTITY(1,1) NOT NULL,
    [EmployeeCode] nvarchar(5)  NOT NULL,
    [LogDateTime] datetime  NOT NULL,
    [Action] int  NOT NULL,
    [HolidayID] int  NOT NULL
);
GO

-- Creating table 'PhysicianAbsents'
CREATE TABLE [dbo].[PhysicianAbsents] (
    [PhysicianAbsentID] int IDENTITY(1,1) NOT NULL,
    [Physician] nvarchar(5)  NOT NULL,
    [Reason] nvarchar(50)  NOT NULL,
    [ServiceID] int  NULL,
    [BeginDate] datetime  NOT NULL,
    [EndDate] datetime  NULL,
    [IsDeleted] bit  NOT NULL
);
GO

-- Creating table 'LogPhysicianAbsents'
CREATE TABLE [dbo].[LogPhysicianAbsents] (
    [LogPhysicianAbsentID] int IDENTITY(1,1) NOT NULL,
    [EmployeeCode] nvarchar(5)  NOT NULL,
    [LogDateTime] datetime  NOT NULL,
    [Action] int  NOT NULL,
    [PhysicianAbsentID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [PhysicianScheduleID] in table 'PhysicianSchedules'
ALTER TABLE [dbo].[PhysicianSchedules]
ADD CONSTRAINT [PK_PhysicianSchedules]
    PRIMARY KEY CLUSTERED ([PhysicianScheduleID] ASC);
GO

-- Creating primary key on [ServiceID] in table 'Services'
ALTER TABLE [dbo].[Services]
ADD CONSTRAINT [PK_Services]
    PRIMARY KEY CLUSTERED ([ServiceID] ASC);
GO

-- Creating primary key on [HolidayID] in table 'Holidays'
ALTER TABLE [dbo].[Holidays]
ADD CONSTRAINT [PK_Holidays]
    PRIMARY KEY CLUSTERED ([HolidayID] ASC);
GO

-- Creating primary key on [AppointmentID] in table 'Appointments'
ALTER TABLE [dbo].[Appointments]
ADD CONSTRAINT [PK_Appointments]
    PRIMARY KEY CLUSTERED ([AppointmentID] ASC);
GO

-- Creating primary key on [HN] in table 'AppointedPatients'
ALTER TABLE [dbo].[AppointedPatients]
ADD CONSTRAINT [PK_AppointedPatients]
    PRIMARY KEY CLUSTERED ([HN] ASC);
GO

-- Creating primary key on [AppointmentDetailID] in table 'AppointmentDetails'
ALTER TABLE [dbo].[AppointmentDetails]
ADD CONSTRAINT [PK_AppointmentDetails]
    PRIMARY KEY CLUSTERED ([AppointmentDetailID] ASC);
GO

-- Creating primary key on [PreOrderID] in table 'PreOrders'
ALTER TABLE [dbo].[PreOrders]
ADD CONSTRAINT [PK_PreOrders]
    PRIMARY KEY CLUSTERED ([PreOrderID] ASC);
GO

-- Creating primary key on [LogAppointmentID] in table 'LogAppointments'
ALTER TABLE [dbo].[LogAppointments]
ADD CONSTRAINT [PK_LogAppointments]
    PRIMARY KEY CLUSTERED ([LogAppointmentID] ASC);
GO

-- Creating primary key on [LogPhysicianScheduleID] in table 'LogPhysicianSchedules'
ALTER TABLE [dbo].[LogPhysicianSchedules]
ADD CONSTRAINT [PK_LogPhysicianSchedules]
    PRIMARY KEY CLUSTERED ([LogPhysicianScheduleID] ASC);
GO

-- Creating primary key on [LogHolidayID] in table 'LogHolidays'
ALTER TABLE [dbo].[LogHolidays]
ADD CONSTRAINT [PK_LogHolidays]
    PRIMARY KEY CLUSTERED ([LogHolidayID] ASC);
GO

-- Creating primary key on [PhysicianAbsentID] in table 'PhysicianAbsents'
ALTER TABLE [dbo].[PhysicianAbsents]
ADD CONSTRAINT [PK_PhysicianAbsents]
    PRIMARY KEY CLUSTERED ([PhysicianAbsentID] ASC);
GO

-- Creating primary key on [LogPhysicianAbsentID] in table 'LogPhysicianAbsents'
ALTER TABLE [dbo].[LogPhysicianAbsents]
ADD CONSTRAINT [PK_LogPhysicianAbsents]
    PRIMARY KEY CLUSTERED ([LogPhysicianAbsentID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ServiceID] in table 'PhysicianSchedules'
ALTER TABLE [dbo].[PhysicianSchedules]
ADD CONSTRAINT [FK_ServicePhysicianSchedule]
    FOREIGN KEY ([ServiceID])
    REFERENCES [dbo].[Services]
        ([ServiceID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ServicePhysicianSchedule'
CREATE INDEX [IX_FK_ServicePhysicianSchedule]
ON [dbo].[PhysicianSchedules]
    ([ServiceID]);
GO

-- Creating foreign key on [HN] in table 'Appointments'
ALTER TABLE [dbo].[Appointments]
ADD CONSTRAINT [FK_AppointedPatientAppointment]
    FOREIGN KEY ([HN])
    REFERENCES [dbo].[AppointedPatients]
        ([HN])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AppointedPatientAppointment'
CREATE INDEX [IX_FK_AppointedPatientAppointment]
ON [dbo].[Appointments]
    ([HN]);
GO

-- Creating foreign key on [AppointmentID] in table 'AppointmentDetails'
ALTER TABLE [dbo].[AppointmentDetails]
ADD CONSTRAINT [FK_AppointmentAppointmentDetail]
    FOREIGN KEY ([AppointmentID])
    REFERENCES [dbo].[Appointments]
        ([AppointmentID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AppointmentAppointmentDetail'
CREATE INDEX [IX_FK_AppointmentAppointmentDetail]
ON [dbo].[AppointmentDetails]
    ([AppointmentID]);
GO

-- Creating foreign key on [PreOrderID] in table 'AppointmentDetails'
ALTER TABLE [dbo].[AppointmentDetails]
ADD CONSTRAINT [FK_PreOrderAppointmentDetail]
    FOREIGN KEY ([PreOrderID])
    REFERENCES [dbo].[PreOrders]
        ([PreOrderID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PreOrderAppointmentDetail'
CREATE INDEX [IX_FK_PreOrderAppointmentDetail]
ON [dbo].[AppointmentDetails]
    ([PreOrderID]);
GO

-- Creating foreign key on [ServiceID] in table 'Appointments'
ALTER TABLE [dbo].[Appointments]
ADD CONSTRAINT [FK_ServiceAppointment]
    FOREIGN KEY ([ServiceID])
    REFERENCES [dbo].[Services]
        ([ServiceID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ServiceAppointment'
CREATE INDEX [IX_FK_ServiceAppointment]
ON [dbo].[Appointments]
    ([ServiceID]);
GO

-- Creating foreign key on [HolidayID] in table 'LogHolidays'
ALTER TABLE [dbo].[LogHolidays]
ADD CONSTRAINT [FK_HolidayLogHoliday]
    FOREIGN KEY ([HolidayID])
    REFERENCES [dbo].[Holidays]
        ([HolidayID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_HolidayLogHoliday'
CREATE INDEX [IX_FK_HolidayLogHoliday]
ON [dbo].[LogHolidays]
    ([HolidayID]);
GO

-- Creating foreign key on [PhysicianScheduleID] in table 'LogPhysicianSchedules'
ALTER TABLE [dbo].[LogPhysicianSchedules]
ADD CONSTRAINT [FK_PhysicianScheduleLogPhysicianSchedule]
    FOREIGN KEY ([PhysicianScheduleID])
    REFERENCES [dbo].[PhysicianSchedules]
        ([PhysicianScheduleID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PhysicianScheduleLogPhysicianSchedule'
CREATE INDEX [IX_FK_PhysicianScheduleLogPhysicianSchedule]
ON [dbo].[LogPhysicianSchedules]
    ([PhysicianScheduleID]);
GO

-- Creating foreign key on [AppointmentID] in table 'LogAppointments'
ALTER TABLE [dbo].[LogAppointments]
ADD CONSTRAINT [FK_AppointmentLogAppointment]
    FOREIGN KEY ([AppointmentID])
    REFERENCES [dbo].[Appointments]
        ([AppointmentID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AppointmentLogAppointment'
CREATE INDEX [IX_FK_AppointmentLogAppointment]
ON [dbo].[LogAppointments]
    ([AppointmentID]);
GO

-- Creating foreign key on [ServiceID] in table 'PhysicianAbsents'
ALTER TABLE [dbo].[PhysicianAbsents]
ADD CONSTRAINT [FK_ServicePhysicianAbsent]
    FOREIGN KEY ([ServiceID])
    REFERENCES [dbo].[Services]
        ([ServiceID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ServicePhysicianAbsent'
CREATE INDEX [IX_FK_ServicePhysicianAbsent]
ON [dbo].[PhysicianAbsents]
    ([ServiceID]);
GO

-- Creating foreign key on [PhysicianAbsentID] in table 'LogPhysicianAbsents'
ALTER TABLE [dbo].[LogPhysicianAbsents]
ADD CONSTRAINT [FK_PhysicianAbsentLogPhysicianAbsent]
    FOREIGN KEY ([PhysicianAbsentID])
    REFERENCES [dbo].[PhysicianAbsents]
        ([PhysicianAbsentID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PhysicianAbsentLogPhysicianAbsent'
CREATE INDEX [IX_FK_PhysicianAbsentLogPhysicianAbsent]
ON [dbo].[LogPhysicianAbsents]
    ([PhysicianAbsentID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------